-- @owner: @haomeng802
-- @date: 2023/1/3
-- @testpoint: 二级分区表(list-hash)结合connect by子句

--step1:创建list-hash分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_008 cascade;
create table t_connectby_008(l_id1 int, l_id2 int,l_id3 int,l_id4 varchar(10))
partition by list (l_id1) subpartition by hash (l_id2)
(partition p_list_1 values (-1,-2,-3,-4,-5,-6,-7,-8,-9,-10)
  (subpartition p_hash_1_1,
   subpartition p_hash_1_2,
   subpartition p_hash_1_3),
 partition p_list_2 values (1,2,3,4,5,6,7,8,9,10)
  (subpartition p_hash_2_1,
   subpartition p_hash_2_2,
   subpartition p_hash_2_3,
   subpartition p_hash_2_4,
   subpartition p_hash_2_5),
  partition p_list_3 values (11,12,13,14,15,16,17,18,19,20),
  partition p_list_4 values (21,22,23,24,25,26,27,28,29,30)
  (subpartition p_hash_4_1),
  partition p_list_5 values (default)
  (subpartition p_hash_5_1),
  partition p_list_6 values (31,32,33,34,35,36,37,38,39,40)
  (subpartition p_hash_6_1,
   subpartition p_hash_6_2,
   subpartition p_hash_6_3));

insert into t_connectby_008 values(4,41,4,'a'),(5,54,5,'b'),(8,87,8,'c'),(99,19,9,'d'),(21,11,1,'e'),(4,41,4,'a'),(5,54,5,'b'),(8,87,8,'c'),(9,19,9,'d');

--step2:查询以l_id1为10开始的节点的直属节点  expect:成功
select * from t_connectby_008 start with l_id1 = 5 connect by nocycle prior l_id1 = l_id2;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(l_id4,'*') as path, *, level from t_connectby_008 start with l_id1 = 5 connect by nocycle prior l_id1 = l_id2;

--step4:清理环境   expect:成功
drop table t_connectby_008 cascade;
